/*
 * Copyright (c) 2020 - present, Inspur Genersoft Co., Ltd.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package io.iec.edp.caf.database;

import com.zaxxer.hikari.HikariDataSource;
import io.iec.edp.caf.data.jdbc.connection.ConnectionInfo;
import io.iec.edp.caf.data.jdbc.connection.ConnectionOperator;
import lombok.extern.slf4j.Slf4j;
import lombok.var;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.InvalidResultSetAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;

import java.math.BigDecimal;
import java.net.URL;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * 封装jdbc connection
 *
 * @author guowenchang
 * @date 2020-10-28
 */
@Deprecated
@Slf4j
public class Database {

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private final CafDataSource cafDataSource;
    private final Connection connection;

    //当前database所属的租户和su
    private final String databaseId;

    private List<Statement> statementCache = new ArrayList<>();
    private List<PreparedStatement> preparedStatementCache = new ArrayList<>();


    //外部数据源构造器
    public Database(Connection connection) {
        this.connection = connection;
        this.cafDataSource = new CafDataSource(connection);
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(cafDataSource);
        databaseId = "InternalDatabase";
    }

    //内部数据源构造器
    public Database(Connection connection,String databaseId){
        this.connection = connection;
        this.cafDataSource = new CafDataSource(connection);
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(cafDataSource);
        this.databaseId = databaseId;
    }

    public ConnectionInfo getConnectionInfo(){
        return new ConnectionInfo(connection);
    }
    public ConnectionOperator getConnectionOperator() throws SQLException{
        return new ConnectionOperator(connection);
    }
    public JdbcTemplate getJdbcTemplate() {
        return namedParameterJdbcTemplate.getJdbcTemplate();
    }

//    /**
//     * 提供DatabaseMetadata
//     * @return DatabaseMetadata
//     * @throws SQLException
//     */
//    public DatabaseMetaData getDatabaseMetaData()throws SQLException{
//        Assert.isTrue(!connection.isClosed(), "Connection is already closed.");
//        return connection.getMetaData();
//    }


    /**
     * 执行方法
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public boolean execute(String sql) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "Connection is already closed.");
        Statement statement = connection.createStatement();
        var ret = statement.execute(sql);

        if(statement!=null){
            statement.close();
        }
        return ret;
    }

    /**
     * 执行方法
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public boolean execute(String sql, List<Object> paramList) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "Connection is already closed.");
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (paramList != null) {
            setParam(preparedStatement, paramList);
        }
        var ret = preparedStatement.execute();
        if(preparedStatement!=null){
            preparedStatement.close();
        }
        return ret;
    }

    /**
     * 参数化执行
     * 底层通过PreparedStatement实现
     *
     * @param sql
     * @param paramMap
     * @return
     */
    public boolean execute(String sql, Map<String, Object> paramMap) {
        return this.namedParameterJdbcTemplate.execute(sql, paramMap, ps -> ps.execute());
    }

    /**
     * 查询方法
     * statement放入集合中在release中释放保证resultSet不被关闭
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public ResultSet query(String sql) throws SQLException {
        Statement statement = connection.createStatement();
        var ret = statement.executeQuery(sql);
        statementCache.add(statement);
//        if(statement!=null){
//            statement.close();
//        }
        return ret;
    }

    /**
     * 查询方法
     * preparedStatement放入集合中在release中释放保证resultSet不被关闭
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public ResultSet query(String sql, List<Object> paramList) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (paramList != null) {
            setParam(preparedStatement, paramList);
        }
        var ret = preparedStatement.executeQuery();
        preparedStatementCache.add(preparedStatement);
//        if(preparedStatement!=null){
//            preparedStatement.close();
//        }
        return ret;
    }

    /**
     * 查询方法
     *
     * @param sql
     * @return
     */
    public <T> List<T> query(String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper) {
        return this.namedParameterJdbcTemplate.query(sql, paramMap, rowMapper);
    }

    /**
     * 查询方法
     *
     * @param sql
     * @return
     */
    public <T> T queryForObject(String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper) {
        return this.namedParameterJdbcTemplate.queryForObject(sql, paramMap, rowMapper);
    }

    /**
     * 查询方法
     *
     * @param sql
     * @return
     */
    public <T> T queryForObject(String sql, Map<String, Object> paramMap, Class<T> requiredType) {
        return this.namedParameterJdbcTemplate.queryForObject(sql, paramMap, requiredType);
    }

    /**
     * 查询方法
     *
     * @param sql
     * @return
     */
    public List<Map<String, Object>> queryForList(String sql, Map<String, Object> paramMap) {
        return this.namedParameterJdbcTemplate.queryForList(sql, paramMap);
    }

    /**
     * 查询方法
     *
     * @param sql
     * @return
     */
    public Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap) {
        return this.namedParameterJdbcTemplate.queryForMap(sql, paramMap);
    }

    /**
     * 更新方法
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public int executeUpdate(String sql) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "Connection is already closed.");
        Statement statement = connection.createStatement();

        var ret = statement.executeUpdate(sql);
        if(statement!=null){
            statement.close();
        }
        return ret;

    }

    /**
     * 更新方法
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public int executeUpdate(String sql, List<Object> paramList) throws SQLException {
        Assert.isTrue(!connection.isClosed(), "Connection is already closed.");
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        if (paramList != null) {
            setParam(preparedStatement, paramList);
        }

        var ret = preparedStatement.executeUpdate();
        if(preparedStatement!=null){
            preparedStatement.close();
        }
        return ret;
    }

    /**
     * 更新方法
     *
     * @param sql
     * @return
     */
    public int executeUpdate(String sql, Map<String, Object> paramMap) {
        return this.namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 更新方法
     *
     * @param sql
     * @return
     */
    public int executeUpdate(String sql, Map<String, Object> paramMap, KeyHolder generatedKeyHolder, @Nullable String[] keyColumnNames) {
        return this.namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(paramMap), generatedKeyHolder, keyColumnNames);
    }

    /**
     * 批量更新
     *
     * @param sql
     * @param batchValues
     * @return
     */
    public int[] batchUpdate(String sql, Map<String, ?>[] batchValues) throws SQLException{
        int[] result = null;
        try {
            if(log.isInfoEnabled()) {
                Boolean isClosed = this.connection.isClosed();
                log.info("线程"+Thread.currentThread().getId()+"connection closed status: "+isClosed.toString());
            }
            result = this.namedParameterJdbcTemplate.batchUpdate(sql, batchValues);
        }catch (InvalidResultSetAccessException e){
            log.error("线程"+Thread.currentThread().getId()+"batchUpdate InvalidResultSetAccessException:"+ e.getMessage());
            throw e;
        }catch (DataAccessException e){
            log.error("线程"+Thread.currentThread().getId()+"batchUpdate DataAccessException:"+(SQLException)e.getCause());
            throw e;
        }catch (SQLException e){
            log.error("线程"+Thread.currentThread().getId()+"connection Sql error:"+e.getMessage());
            throw e;
        }
        return result;
    }

    /**
     * 带参数类型的批量更新
     *
     * @param sql
     * @param batchArgs
     * @return
     */
    public int[] batchUpdate(String sql,SqlParameterSource[] batchArgs) throws SQLException {
        int[] result = null;
        try {
            if(log.isInfoEnabled()) {
                Boolean isClosed = this.connection.isClosed();
                log.info("线程"+Thread.currentThread().getId()+"connection closed status: "+isClosed.toString());
            }
            result = this.namedParameterJdbcTemplate.batchUpdate(sql, batchArgs);
        }catch (InvalidResultSetAccessException e){
            log.error("线程"+Thread.currentThread().getId()+"batchUpdate InvalidResultSetAccessException:"+ e.getMessage());
            throw e;
        }catch (DataAccessException e){
            log.error("线程"+Thread.currentThread().getId()+"batchUpdate DataAccessException:"+(SQLException)e.getCause());
            throw e;
        }catch (SQLException e){
            log.error("线程"+Thread.currentThread().getId()+"connection Sql error:"+e.getMessage());
            throw e;
        }
        return result;
    }


    public void beginTrans() throws SQLException {
        var connection = this.cafDataSource.getConnection();
        if(connection!=null && connection.isClosed()==false){
            connection.setAutoCommit(false);
        }

    }

    public void commit() throws SQLException {
        var connection = this.cafDataSource.getConnection();
        if(connection!=null && connection.isClosed()==false){
            if(connection.getAutoCommit()==false){
                connection.commit();
            }else{
                throw new RuntimeException("The current link AutoCommit is true, there is no need to manually commit the transaction");
            }
        }
    }

    public void rollBack() throws SQLException {
        var connection = this.cafDataSource.getConnection();
        if(connection!=null && connection.isClosed()==false){
            if(connection.getAutoCommit()==false){
                connection.rollback();
            }else{
                throw new RuntimeException("The current link AutoCommit is true, there is no need to manually commit the transaction");
            }
        }
    }

    /**
     * 设置参数
     **/
    private void setParam(PreparedStatement preparedStatement, List<Object> paramList) throws SQLException {
        int order = 0;
        for (Object obj : paramList) {
            order++;
            if (obj instanceof Integer) {
                preparedStatement.setInt(order, (Integer) obj);
            } else if (obj instanceof Byte) {
                preparedStatement.setByte(order, (Byte) obj);
            } else if (obj instanceof Short) {
                preparedStatement.setShort(order, (Short) obj);
            } else if (obj instanceof Long) {
                preparedStatement.setLong(order, (Long) obj);
            } else if (obj instanceof Double) {
                preparedStatement.setDouble(order, (Double) obj);
            } else if (obj instanceof Float) {
                preparedStatement.setFloat(order, (Float) obj);
            } else if (obj instanceof byte[]) {
                preparedStatement.setBytes(order, (byte[]) obj);
            } else if (obj instanceof NClob) {
                preparedStatement.setNClob(order, (NClob) obj);
            } else if (obj instanceof Blob) {
                preparedStatement.setBlob(order, (Blob) obj);
            } else if (obj instanceof Clob) {
                preparedStatement.setClob(order, (Clob) obj);
            } else if (obj instanceof Boolean) {
                preparedStatement.setBoolean(order, (Boolean) obj);
            } else if (obj instanceof Array) {
                preparedStatement.setArray(order, (Array) obj);
            } else if (obj instanceof java.sql.Date) {
                preparedStatement.setDate(order, (java.sql.Date) obj);
            } else if (obj instanceof Time) {
                preparedStatement.setTime(order, (Time) obj);
            } else if (obj instanceof Timestamp) {
                preparedStatement.setTimestamp(order, (Timestamp) obj);
            } else if (obj instanceof Date) {
                preparedStatement.setDate(order, transformDate((Date) obj));
            } else if (obj instanceof Calendar) {
                preparedStatement.setDate(order, transformDate(((Calendar) obj).getTime()), (Calendar) obj);
            } else if (obj instanceof BigDecimal) {
                preparedStatement.setBigDecimal(order, (BigDecimal) obj);
            } else if (obj instanceof String) {
                preparedStatement.setString(order, (String) obj);
            } else if (obj instanceof URL) {
                preparedStatement.setURL(order, (URL) obj);
            } else {
                throw new RuntimeException("not support this type: " + obj.getClass());
            }
        }
    }

    private java.sql.Date transformDate(Date date) {
        return new java.sql.Date(date.getTime());
    }

    /**
     * 关闭statement
     * @throws SQLException
     */
    private void closeStatementCache() throws SQLException{
        for(Statement statement:statementCache){
            if(statement!=null){
                statement.close();
            }
        }

        statementCache.clear();

    }

    /**
     * 关闭preparedStatement
     * @throws SQLException
     */
    private void closePreparedStatementCache() throws SQLException {
        for(PreparedStatement preparedStatement:preparedStatementCache){
            if(preparedStatement!=null){
                preparedStatement.close();
            }
        }

        preparedStatementCache.clear();
    }

    /**
     * 暴露给外部使用 避免大量创建产生未关闭的statement
     * @throws SQLException
     */
    public void closeStatement() throws SQLException{
        closeStatementCache();
        closePreparedStatementCache();
    }

    void release(){
        try{
            closeStatement();
        }catch (SQLException e){
            log.error("Statement close failure:"+e.getMessage(),e);
            //throw e;
        }finally {
            if(log.isInfoEnabled()) log.info("\n线程"+Thread.currentThread().getId()+"当前连接信息："+connection.toString()+"已释放连接");
            this.cafDataSource.release();
        }
    }

//    void evictConnection(){
//
//        HikariDataSource hds = TenantDataSourceProvider.getHikariDataSource(databaseId);
//        if(hds!=null){
//            hds.evictConnection(connection);
//        }
//        release();
//    }
//
//    void destroyConnection(){
//        Connection realCon;
//        try{
//            //获取真实物理连接
//            realCon = connection.unwrap(Connection.class);
//            //关闭物理连接
//            realCon.close();
//            try {
//                release();
//            }catch (Throwable e){
//                //doNothing
//            }
//        }catch (Throwable e){
//            log.error("数据库连接无法拆解/关闭："+connection.getClass().getName());
//            throw new RuntimeException(e.getMessage());
//        }
//    }
}
